﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Collections;

namespace RESP_IIWaterAssessment
{
    public partial class WaterSupplyFormReport : Form
    {
        public WaterSupplyFormReport()
        {
            InitializeComponent();
        }

        int avpID, regID, canalID = 0, year, month;
        string[] months;

        private void WaterSupplyFormReport_Load(object sender, EventArgs e)
        {
            cbRegion.SelectedIndexChanged -= cbRegion_SelectedIndexChanged;
            cbRegion.DataSource = UtilClass.FillCBRegions();
            cbRegion.DisplayMember = "Name";
            cbRegion.ValueMember = "ID";

            if (InformationClass.isAdmin)
            {
                cbRegion.SelectedIndex = -1;
                cbRegion.Text = string.Empty;
                cbRegion.SelectedIndexChanged += new EventHandler(cbRegion_SelectedIndexChanged);
            }
            else
            {
                chbRegion.Checked = true;
                cbRegion.Enabled = chbRegion.Enabled = false;
                regID = InformationClass.regionID;
                cbRegion.SelectedIndexChanged += new EventHandler(cbRegion_SelectedIndexChanged);
                cbRegion.SelectedValue = regID;
                cbRegion_SelectedIndexChanged(cbRegion, null);
                cbAVP.Enabled = true;
                cbAVP.Text = string.Empty;
                //toolStrip1.Visible = false;
            }


            if (InformationClass.Lang)
                months = new string[] { "Январь", "Февраль", "Март", "Апрель", "Май", "Июнь", "Июль", "Август", "Сентябрь", "Октябрь", "Ноябрь", "Декабрь" };
            else
                months = new string[] { "Январ", "Феврал", "Март", "Апрел", "Май", "Июн", "Июл", "Август", "Сентябр", "Октябр", "Ноябр", "Декабр" };

            for (int i = 1; i <= 12; i++)
            {
                var m = new
                {
                    ID = i,
                    Name = months[i - 1]
                };
                cbMonth.Items.Add(m);
            }
            cbMonth.DisplayMember = "Name";
            cbMonth.ValueMember = "ID";

            for (int i = 1991; i < 2100; i++)
            {
                cbYear.Items.Add(i);
            }
            cbMonth.SelectedIndex = cbMonth.FindStringExact(months[DateTime.Now.Month - 1]);
            cbYear.SelectedIndex = cbYear.FindStringExact(DateTime.Now.Year.ToString());

            cbCanal.Enabled = false;

            chbMonth.Checked = true;
        }

        private void cbRegion_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbRegion.SelectedIndex >= 0)
            {
                cbAVP.Enabled = true;
                regID = Convert.ToInt32(cbRegion.SelectedValue);
                cbAVP.SelectedIndexChanged -= cbAVP_SelectedIndexChanged;
                cbAVP.DataSource = UtilClass.FillAVP(regID);
                cbAVP.ValueMember = "ID";
                cbAVP.DisplayMember = "Name";
                cbAVP.SelectedIndex = -1;
                cbAVP.Text = string.Empty;
                cbAVP.SelectedIndexChanged += new EventHandler(cbAVP_SelectedIndexChanged);
            }
        }

        private void cbAVP_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbAVP.SelectedIndex >= 0)
            {
                avpID = Convert.ToInt32(cbAVP.SelectedValue);
                cbCanal.Enabled = true;
                cbCanal.SelectedIndexChanged -= cbCanal_SelectedIndexChanged;
                cbCanal.DataSource = DBClass.Canals
                    .Where(wh => wh.AVPID == avpID)
                    .Select(se => new { ID = se.ID, Name = se.Name })
                    .OrderBy(o => o.Name)
                    .ToArray();
                cbCanal.DisplayMember = "Name";
                cbCanal.ValueMember = "ID";
                cbCanal.SelectedIndex = -1;
                cbCanal.Text = string.Empty;
                cbCanal.SelectedIndexChanged += new EventHandler(cbCanal_SelectedIndexChanged);
            }
        }

        private void cbCanal_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbCanal.SelectedIndex >= 0)
            {
                canalID = Convert.ToInt32(cbCanal.SelectedValue);
                //int m = cbMonth.SelectedIndex + 1;
                //int y = Convert.ToInt32(cbYear.Text);
                //ShowWaterSupply(canalID, m, y);
            }
        }

        private void cbYear_SelectedIndexChanged(object sender, EventArgs e)
        {
            year = Convert.ToInt32(cbYear.Text);
        }

        private void cbMonth_SelectedIndexChanged(object sender, EventArgs e)
        {
            month = cbMonth.SelectedIndex + 1;
        }

        private void chbRegion_CheckedChanged(object sender, EventArgs e)
        {
            cbRegion.Enabled = chbRegion.Checked;
        }

        private void chbAVP_CheckedChanged(object sender, EventArgs e)
        {
            cbAVP.Enabled = chbAVP.Checked;
        }

        private void chbCanal_CheckedChanged(object sender, EventArgs e)
        {
            cbCanal.Enabled = chbCanal.Checked;
        }

        private void chbYear_CheckedChanged(object sender, EventArgs e)
        {

        }

        private void chbMonth_CheckedChanged(object sender, EventArgs e)
        {
            cbMonth.Enabled = chbMonth.Checked;
            if (chbMonth.Checked)
                chbPeriod.Checked = false;

        }

        private void chbPeriod_CheckedChanged(object sender, EventArgs e)
        {
            groupBoxPeriod.Enabled = chbPeriod.Checked;
            if (chbPeriod.Checked)
                chbMonth.Checked = false;

        }

        private void buttonShow_Click(object sender, EventArgs e)
        {
            //if (!chbCanal.Checked)
            //{
            //    string msg = InformationClass.Lang ? "Выбирете канал!" : "Выбирете канал узб";
            //    MessageBox.Show(msg);
            //    return;
            //}

            dgvWaterSupply.DataSource = null;
            dgvWaterSupply.Columns.Clear();

            int? rID, aID, cID, y;
            bool? period;
            DateTime? dt;

            if (chbPeriod.Checked)
                period = rbVeget.Checked;
            else
                period = null;

            if (chbRegion.Checked && regID > 0)
                rID = regID;
            else
                rID = null;
            if (chbAVP.Checked && avpID > 0)
                aID = avpID;
            else
                aID = null;
            if (chbCanal.Checked && canalID > 0)
                cID = canalID;
            else
                cID = null;

            if (chbMonth.Checked)
            {
                dt = new DateTime(year, month, 1);
                period = null;
            }
            else
            {
                dt = new DateTime(year, 1, 1);
                period = rbVeget.Checked;
            }
            try
            {
                dgvWaterSupply.DataSource = DBClass.RESPContext.WaterSupplyReport(rID, aID, cID, period, dt);

                AddColumn();

                 if (InformationClass.Lang)
                {
                    dgvWaterSupply.Columns["Date"].HeaderText = "Декада";
                    dgvWaterSupply.Columns["Area"].HeaderText = "Площадь (га)";
                    if(aID!=null)
                     {
                     dgvWaterSupply.Columns["AVPName"].HeaderText = "АВП";
                     dgvWaterSupply.Columns["CanalName"].HeaderText = "Канал";
                     }
                    else
                     {
                      dgvWaterSupply.Columns["AVPName"].HeaderText = "Район";
                      dgvWaterSupply.Columns["CanalName"].HeaderText = "АВП";
                     }
                    dgvWaterSupply.Columns["WaterPlan"].HeaderText = "План (тыс.м3)";
                    dgvWaterSupply.Columns["WaterLimit"].HeaderText = "Лимит (тыс.м3)";
                    dgvWaterSupply.Columns["WaterFact"].HeaderText = "Факт (тыс.м3)";
                    dgvWaterSupply.Columns["FactPlan"].HeaderText = "% факт/план";
                    dgvWaterSupply.Columns["FactLimit"].HeaderText = "% факт/лимит";
                }
                else
                {
                    dgvWaterSupply.Columns["Date"].HeaderText = "Давр";
                    dgvWaterSupply.Columns["Area"].HeaderText = "Майдон (га)";
                    dgvWaterSupply.Columns["AVPName"].HeaderText = "СИУ номи";
                    if (aID != null)
                    {
                        dgvWaterSupply.Columns["AVPName"].HeaderText = "АВП";
                        dgvWaterSupply.Columns["CanalName"].HeaderText = "Канал";
                    }
                    else
                    {
                        dgvWaterSupply.Columns["AVPName"].HeaderText = "Район";
                        dgvWaterSupply.Columns["CanalName"].HeaderText = "АВП";
                    }
                    dgvWaterSupply.Columns["WaterPlan"].HeaderText = "Режа (тыс.м3)";
                    dgvWaterSupply.Columns["WaterLimit"].HeaderText = "Лимит (тыс.м3)";
                    dgvWaterSupply.Columns["WaterFact"].HeaderText = "Амалда (тыс.м3)";
                    dgvWaterSupply.Columns["FactPlan"].HeaderText = "% Амалда/режада";
                    dgvWaterSupply.Columns["FactLimit"].HeaderText = "% Амалда/лимит";
                }
            }
            catch
            {
                MessageBox.Show(msgClass.msgUncorrectDataRepport);
                dgvWaterSupply.DataSource = null;
            }
        }

        private void AddColumn()
        {
            string name = "mounting";
            string header = InformationClass.Lang ? "Нарастающий" : "Ошиб бориши";
            dgvWaterSupply.Columns.Add(name, header);
            dgvWaterSupply.Columns[name].DisplayIndex = dgvWaterSupply.ColumnCount - 3;

            if (dgvWaterSupply.RowCount > 0)
                AddData();
        }

        private void AddData()
        {
            for (int rowInd = 0; rowInd < dgvWaterSupply.RowCount; rowInd++)
            {
                //int dec = Convert.ToInt32(dgvWaterSupply["Date", rowInd].Value.ToString()[0].ToString());
                int dec = 2;
                DateTime dtr,dt_veg,dt_noveg;
                dtr = DateTime.Parse(dgvWaterSupply["Date", rowInd].Value.ToString());
                dt_veg = DateTime.Parse(1 + "." + 4 + "." + cbYear.Text);
                if (dtr==dt_veg) dec = 0;
                string c_y = cbYear.Text;
                string d_y = dtr.Year.ToString();
                if (d_y != c_y) dec = 3;
                //dgvWaterSupply["mounting", rowInd].Value = dec;
                switch (dec)
                {
                    case 0:
                        dgvWaterSupply["mounting", rowInd].Value = dgvWaterSupply["WaterFact", rowInd].Value;
                        break;
                    case 1:
                    case 2:
                        dgvWaterSupply["mounting", rowInd].Value = (decimal)dgvWaterSupply["WaterFact", rowInd].Value + (decimal)dgvWaterSupply["mounting", rowInd - 1].Value;
                        break;
                    default:
                        break;
                }
            }
        }

        private void dgvWaterSupply_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            if (e.ColumnIndex.Equals(dgvWaterSupply.Columns["CanalName"].Index) && e.Value != null)
            {
                string str_e = (string)e.Value;
                string str_r = "яя";
                if (str_r != str_e)
                {
                    e.Value = e.Value;
                }
                else
                {
                    e.Value = " ";
                }
            }
            if (e.ColumnIndex.Equals(dgvWaterSupply.Columns["Date"].Index) && e.Value != null)
            {
                switch (((DateTime)e.Value).Day)
                {
                    case 1:
                        e.Value = "1 декада (" + months[((DateTime)e.Value).Month - 1] + ")";
                        break;
                    case 11:
                        e.Value = "2 декада (" + months[((DateTime)e.Value).Month - 1] + ")";
                        break;
                    case 21:
                        e.Value = "3 декада (" + months[((DateTime)e.Value).Month - 1] + ")";
                        break;
                    case 2:
                        e.Value = "Всего ";
                        break;
                    default:
                        e.Value = "Всего";

                        break;
                }

            }
            if ((e.ColumnIndex.Equals(dgvWaterSupply.Columns["FactPlan"].Index) || e.ColumnIndex.Equals(dgvWaterSupply.Columns["FactLimit"].Index)) && e.Value != null)
            {
                e.Value = ((decimal)e.Value).ToString("N2");
            }
        }

        private void buttonExcel_Click(object sender, EventArgs e)
        {
            if (dgvWaterSupply.RowCount > 0)
            {
                MessageBox.Show(msgClass.msgWaiting);
                GetReport();
                excelapp.Visible = true;
            }
        }

        Sheets excelsheets;
        Worksheet excelworksheet;
        Range range_sel;
        Range range_dest;
        Worksheet excelworksheet_dest;
        Microsoft.Office.Interop.Excel.Application excelapp;
        Workbook excelappworkbook;

        private void GetReport()
        {
            excelapp = new Microsoft.Office.Interop.Excel.Application();
            excelapp.SheetsInNewWorkbook = 1;

            if (InformationClass.Lang)
               if (chbAVP.Checked && avpID > 0)
                   excelapp.Workbooks.Add(System.Windows.Forms.Application.StartupPath + @"\Templates\i12r14.xls");
               else
                   excelapp.Workbooks.Add(System.Windows.Forms.Application.StartupPath + @"\Templates\i12r14s.xls");
            else
               if (chbAVP.Checked && avpID > 0)
                   excelapp.Workbooks.Add(System.Windows.Forms.Application.StartupPath + @"\Templates\i12r14uz.xls");
               else
                   excelapp.Workbooks.Add(System.Windows.Forms.Application.StartupPath + @"\Templates\i12r14uzs.xls");

            excelappworkbook = excelapp.Workbooks[1];
            excelsheets = excelappworkbook.Worksheets;// .Worksheets[0]; //Sheets 
            int numPage = 1;//, left = 4, top = 8;

            excelworksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelsheets.get_Item(numPage);

            excelworksheet.Cells.NumberFormat = "@";

            if (chbRegion.Checked)
                excelworksheet.Cells[4, 3] = cbRegion.Text;
            if (chbAVP.Checked)
                excelworksheet.Cells[5, 3] = cbAVP.Text;

            string per = " "+cbYear.Text;

            if (chbMonth.Checked)
                per = cbMonth.Text + per;
            else
            {
                if (rbVeget.Checked)
                    per = rbVeget.Text + per;
                else
                    per = rbNotVeget.Text + per;
            }

            excelworksheet.Cells[6, 2] = per;
            excelworksheet.Copy(Type.Missing, (Microsoft.Office.Interop.Excel.Worksheet)excelworksheet);
            excelworksheet_dest = (Worksheet)excelsheets[2];
            if (chbAVP.Checked && avpID > 0)
            {
                //top = 7;
                range_sel = (Range)excelworksheet_dest.get_Range(excelworksheet_dest.Cells[16, 1], excelworksheet_dest.Cells[21, 7]);
                range_sel.Delete(XlDeleteShiftDirection.xlShiftToLeft);
                range_sel = (Range)excelworksheet_dest.get_Range(excelworksheet_dest.Cells[8, 8], excelworksheet_dest.Cells[15, 8]);
                range_sel.Delete(XlDeleteShiftDirection.xlShiftUp);
                GetRecords();
            }
            else
            {
                range_sel = (Range)excelworksheet_dest.get_Range(excelworksheet_dest.Cells[15, 1], excelworksheet_dest.Cells[20, 7]);
                range_sel.Delete(XlDeleteShiftDirection.xlShiftToLeft);
                range_sel = (Range)excelworksheet_dest.get_Range(excelworksheet_dest.Cells[7, 8], excelworksheet_dest.Cells[14, 8]);
                range_sel.Delete(XlDeleteShiftDirection.xlShiftUp);
                GetRecords();
            }

            //GetRecord();
            excelapp.DisplayAlerts = false;
            excelworksheet.Delete();
        }
        private void GetRecords()
        {
            int top = 6, left = 4,icel=1;
            if (chbAVP.Checked && avpID > 0)
            {
                top = 7;
                icel = 1;
            }
            ArrayList months, channels;
            CountValueDateColumns("Date", out months);
            CountValueStringColumns("CanalName", out channels);
            if (months.Count - icel == 0)
            {
                icel = 0;
            }
            for (int j = 0; j < months.Count-icel; j++)
            {   
                if (j>0)
                {
                range_sel = (Range)excelworksheet_dest.get_Range(excelworksheet_dest.Cells[top+1, left+1],
                                                                 excelworksheet_dest.Cells[top+8, left+3]);
                range_sel.Copy(excelworksheet_dest.Cells[top+1, left+1+j*3]);
                range_dest = (Range)excelworksheet_dest.get_Range(excelworksheet_dest.Cells[top + 1, left + 1 + j * 3],
                                                                 excelworksheet_dest.Cells[top + 8, left + 3 + j * 3]);
                range_dest.ColumnWidth = range_sel.ColumnWidth;
                }
                excelworksheet_dest.Cells[top+1, left+1+j*3]=this.months[(int)months[j+icel] - 1];
            }
            range_sel = (Range)excelworksheet.get_Range(excelworksheet.Cells[top + 1, left + 4],
                                                             excelworksheet.Cells[top + 8, left + 4]);
            range_sel.Copy(excelworksheet_dest.Cells[top + 1, left + 1 + (months.Count - icel) * 3]);
            range_dest = (Range)excelworksheet_dest.get_Range(excelworksheet_dest.Cells[top + 1, left + 1 + (months.Count - icel) * 3],
                                                             excelworksheet_dest.Cells[top + 8, left + 1 + (months.Count - icel) * 3]);
            range_dest.ColumnWidth = range_sel.ColumnWidth;

            for (int i = 0; i < channels.Count; i++)
            {
                if (i > 0)
                {
                    range_sel = (Range)excelworksheet_dest.get_Range(excelworksheet_dest.Cells[top + 3, 1],
                                                                     excelworksheet_dest.Cells[top + 8, left + 1 + (months.Count - icel) * 3]);
                    range_sel.Copy(excelworksheet_dest.Cells[top + 3+i*6, 1]);
                }
                range_sel = (Range)excelworksheet_dest.get_Range(excelworksheet_dest.Cells[top + 3, left + 1 + (months.Count - icel) * 3],
                                                                     excelworksheet_dest.Cells[top + 8, left + 1 + (months.Count - icel) * 3]);
                range_sel.NumberFormat = "0";
                excelworksheet_dest.Cells[top+3+i*6,2]=channels[i];
                if (i == channels.Count-1)
                {
                    excelworksheet_dest.Cells[top + 3 + i * 6, 2] = excelworksheet.Cells[top + 9, 2];
                    
                }
            }
 // заполнение значениями
            float area=1;
            
            for (int rc = 0; rc < dgvWaterSupply.RowCount; rc++)
            {
                int m = ((DateTime)dgvWaterSupply["Date", rc].Value).Month;
                int day;
                int indcell;
                switch (((DateTime)dgvWaterSupply["Date", rc].Value).Day)
                {
                    case 1:
                        day = 1;
                        indcell = 3 * (months.IndexOf(m)-icel) + day + left;
                        break;
                    case 11:
                        day = 2;
                        indcell = 3 * (months.IndexOf(m) - icel) + day + left;
                        break;
                    case 21:
                        day = 3;
                        indcell = 3 * (months.IndexOf(m) - icel) + day + left;
                        break;
                    case  2:
                        day = 12;
                        indcell = 3 * (months.Count - icel) + left + 1;
                        break;
                    default:
                        day = 0;
                        indcell = 3 * (months.Count - icel) + left;
                        break;
                }


                int chanInd = channels.IndexOf(dgvWaterSupply["CanalName", rc].Value.ToString());
                if (day != 0 && day!=12)
                {
                    excelworksheet_dest.Cells[3 + (chanInd * 6) + top, indcell] = dgvWaterSupply["WaterPlan", rc].Value;
                    //addBorder(excelworksheet.Cells[3 + (chanInd * 8) + top, indcell]);

                    excelworksheet_dest.Cells[4 + (chanInd * 6) + top, indcell] = dgvWaterSupply["WaterLimit", rc].Value;
                    //addBorder(excelworksheet.Cells[4 + (chanInd * 8) + top, indcell]);

                    excelworksheet_dest.Cells[5 + (chanInd * 6) + top, indcell] = dgvWaterSupply["WaterFact", rc].Value;
                    //addBorder(excelworksheet.Cells[5 + (chanInd * 8) + top, indcell]);

                    excelworksheet_dest.Cells[7 + (chanInd * 6) + top, indcell] = dgvWaterSupply["FactPlan", rc].Value;
                    //addBorder(excelworksheet.Cells[7 + (chanInd * 8) + top, indcell]);

                    excelworksheet_dest.Cells[8 + (chanInd * 6) + top, indcell] = dgvWaterSupply["FactLimit", rc].Value;
                    //addBorder(excelworksheet.Cells[8 + (chanInd * 8) + top, indcell]);

                    excelworksheet_dest.Cells[6 + (chanInd * 6) + top, indcell] = dgvWaterSupply["mounting", rc].Value;
                    //addBorder(excelworksheet.Cells[6 + (chanInd * 8) + top, indcell]);

                }
                if (day == 1)
                {
                    excelworksheet_dest.Cells[3 + (chanInd * 6) + top, 3] = dgvWaterSupply["Area", rc].Value;
                    area = float.Parse(dgvWaterSupply["Area", rc].Value.ToString());
                }
                //dgvWaterSupply["WaterPlan", rc].Value;

                if (day == 12)
                {
                    excelworksheet_dest.Cells[3 + (chanInd * 6) + top, indcell] =
                        float.Parse(dgvWaterSupply["WaterPlan", rc].Value.ToString())*1000 /area;
                    //addBorder(excelworksheet.Cells[3 + (chanInd * 8) + top, indcell]);

                    excelworksheet_dest.Cells[4 + (chanInd * 6) + top, indcell] =
                        float.Parse(dgvWaterSupply["WaterLimit", rc].Value.ToString()) * 1000 / area;
                    //addBorder(excelworksheet.Cells[4 + (chanInd * 8) + top, indcell]);

                    excelworksheet_dest.Cells[5 + (chanInd * 6) + top, indcell] =
                        float.Parse(dgvWaterSupply["WaterFact", rc].Value.ToString()) * 1000 / area;
                    //addBorder(excelworksheet.Cells[5 + (chanInd * 8) + top, indcell]);

                    excelworksheet_dest.Cells[7 + (chanInd * 6) + top, indcell] = dgvWaterSupply["FactPlan", rc].Value;
                    //addBorder(excelworksheet.Cells[7 + (chanInd * 8) + top, indcell]);

                    excelworksheet_dest.Cells[8 + (chanInd * 6) + top, indcell] = dgvWaterSupply["FactLimit", rc].Value;
                    //addBorder(excelworksheet.Cells[8 + (chanInd * 8) + top, indcell]);

                    excelworksheet_dest.Cells[6 + (chanInd * 6) + top, indcell] = dgvWaterSupply["mounting", rc].Value;
                    //addBorder(excelworksheet.Cells[6 + (chanInd * 8) + top, indcell]);

                }
            }

        }
        private void GetRecord()
        {
            int top = 6, left = 4;

            ArrayList months, channels;
            CountValueDateColumns("Date", out months);
            CountValueStringColumns("CanalName", out channels);

            //x = (3*m+1 + 0)-(3*m+3+0) y= (1+k*3+0)

            for (int i = 0; i < channels.Count; i++)
            {
                for (int j = 0; j < months.Count; j++)
                {
                    excelworksheet.get_Range(excelworksheet.Cells[1 + (i * 8) + top, 3 * j + 1 + left], excelworksheet.Cells[1 + (i * 8) + top, 3 * j + 3 + left]).Merge(Type.Missing);
                    addBorder(excelworksheet.Cells[1 + (i * 8) + top, 3 * j + 1 + left]);
                    addBorder(excelworksheet.Cells[1 + (i * 8) + top, 3 * j + 2 + left]);
                    addBorder(excelworksheet.Cells[1 + (i * 8) + top, 3 * j + 3 + left]);

                    excelworksheet.Cells[1 + (i * 8) + top, 3 * j + 1 + left] = this.months[(int)months[j] - 1] + " (" + channels[i] + ")";

                    excelworksheet.Cells[2 + (i * 8) + top, 3 * j + 1 + left] = "I";
                    addBorder(excelworksheet.Cells[2 + (i * 8) + top, 3 * j + 1 + left]);
                    excelworksheet.Cells[2 + (i * 8) + top, 3 * j + 2 + left] = "II";
                    addBorder(excelworksheet.Cells[2 + (i * 8) + top, 3 * j + 2 + left]);
                    excelworksheet.Cells[2 + (i * 8) + top, 3 * j + 3 + left] = "III";
                    addBorder(excelworksheet.Cells[2 + (i * 8) + top, 3 * j + 3 + left]);
                }
                excelworksheet.Cells[1 + (i * 8) + top, 3 * (months.Count-1) + 3 + left + 1] = "Всего";
                addBorder(excelworksheet.Cells[1 + (i * 8) + top, 3 * (months.Count - 1) + 3 + left+1]);
                excelworksheet.Cells[3 + (i * 8) + top, 2] = excelworksheet.Cells[10, 2];
                addBorder(excelworksheet.Cells[3 + (i * 8) + top, 2]);

                excelworksheet.Cells[4 + (i * 8) + top, 2] = excelworksheet.Cells[11, 2];
                addBorder(excelworksheet.Cells[4 + (i * 8) + top, 2]);

                excelworksheet.Cells[5 + (i * 8) + top, 2] = excelworksheet.Cells[12, 2];
                addBorder(excelworksheet.Cells[5 + (i * 8) + top, 2]);

                excelworksheet.Cells[6 + (i * 8) + top, 2] = excelworksheet.Cells[13, 2];
                addBorder(excelworksheet.Cells[6 + (i * 8) + top, 2]);

                excelworksheet.Cells[7 + (i * 8) + top, 2] = excelworksheet.Cells[14, 2];
                addBorder(excelworksheet.Cells[7 + (i * 8) + top, 2]);

                excelworksheet.Cells[8 + (i * 8) + top, 2] = excelworksheet.Cells[15, 2];
                addBorder(excelworksheet.Cells[8 + (i * 8) + top, 2]);
            }

            //FillReccords

            for (int rc = 0; rc < dgvWaterSupply.RowCount; rc++)
            {
                int m = ((DateTime)dgvWaterSupply["Date", rc].Value).Month;
                int day;
                int indcell;
                switch (((DateTime)dgvWaterSupply["Date", rc].Value).Day)
                {
                    case 1:
                        day = 1;
                        indcell = 3 * months.IndexOf(m) + day + left;
                        break;
                    case 11:
                        day = 2;
                        indcell = 3 * months.IndexOf(m) + day + left;
                        break;
                    case 21:
                        day = 3;
                        indcell = 3 * months.IndexOf(m) + day + left;
                        break;
                    default:
                        day = 0;
                        indcell = 3 * (months.Count-1) + 4 + left;
                        break;
                }


                int chanInd = channels.IndexOf(dgvWaterSupply["CanalName", rc].Value.ToString());

                excelworksheet.Cells[3 + (chanInd * 8) + top, indcell] = dgvWaterSupply["WaterPlan", rc].Value;
                addBorder(excelworksheet.Cells[3 + (chanInd * 8) + top, indcell]);

                excelworksheet.Cells[4 + (chanInd * 8) + top, indcell] = dgvWaterSupply["WaterLimit", rc].Value;
                addBorder(excelworksheet.Cells[4 + (chanInd * 8) + top, indcell]);

                excelworksheet.Cells[5 + (chanInd * 8) + top, indcell] = dgvWaterSupply["WaterFact", rc].Value;
                addBorder(excelworksheet.Cells[5 + (chanInd * 8) + top, indcell]);

                excelworksheet.Cells[7 + (chanInd * 8) + top, indcell] = dgvWaterSupply["FactPlan", rc].Value;
                addBorder(excelworksheet.Cells[7 + (chanInd * 8) + top, indcell]);

                excelworksheet.Cells[8 + (chanInd * 8) + top, indcell] = dgvWaterSupply["FactLimit", rc].Value;
                addBorder(excelworksheet.Cells[8 + (chanInd * 8) + top, indcell]);

                excelworksheet.Cells[6 + (chanInd * 8) + top, indcell] = dgvWaterSupply["mounting", rc].Value;
                addBorder(excelworksheet.Cells[6 + (chanInd * 8) + top, indcell]);



                //dgvWaterSupply["WaterPlan", rc].Value;

            }
        }

        private void addBorder(object p)
        {
            Range tmpRange = excelworksheet.get_Range(p, p);
            tmpRange.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
        }

        //private void button1_Click(object sender, EventArgs e)
        //{
        //    int dtCount = CountValueDateColumns("Date"), obJCount = CountValueStringColumns("Obj");

        //    textBox1.Text = dtCount.ToString();
        //    textBox2.Text = obJCount.ToString();
        //}

        private int CountValueDateColumns(string colName, out ArrayList months)
        {
            int res = 0;

            months = new ArrayList();

            for (int i = 0; i < dgvWaterSupply.RowCount; i++)
            {
                if (!months.Contains(((DateTime)dgvWaterSupply[colName, i].Value).Month))
                    months.Add(((DateTime)dgvWaterSupply[colName, i].Value).Month);
            }
            months.Sort();
            res = months.Count;

            return res;
        }

        private int CountValueStringColumns(string colName, out ArrayList channels)
        {
            int res = 0;

            channels = new ArrayList();

            for (int i = 0; i < dgvWaterSupply.RowCount; i++)
            {
                if (!channels.Contains(dgvWaterSupply[colName, i].Value.ToString()))
                    channels.Add(dgvWaterSupply[colName, i].Value.ToString());
            }
            channels.Sort();
            res = channels.Count;

            return res;
        }

        private void buttonPrint_Click(object sender, EventArgs e)
        {
            if (dgvWaterSupply.RowCount > 0)
                PrintDGV.Print_DataGridView(dgvWaterSupply);
        }
    }
}
